اسناد خزانه (اخزا)

اصلاح شده

2024-06-12

در این بخش می‌خواهیم نمودار از سود مرکب سالانه از اسناد خزانه دولتی یا همان اخزا را به صورت سری زمانی روزانه رسم کنیم.

آماده سازی داده

نکته. اگر از محیط ژوپیتر استفاده می‌کنید این کد را اجرا کنید.

import nest_asyncio
nest_asyncio.apply()

ابتدا بسته را به آخرین ورژن بروز رسانی می‌کنیم.

%pip install --upgrade codal_tsetmc

پکیج های مورد نیاز را در محیط فراخوانی می‌کنیم.

import pandas as pd
import numpy as np
import jalali_pandas

import codal_tsetmc as ct

داده‌های مورد نیاز برای محاسبه سود سالانه هر اوراق را در دیتابیس بروز می‌کنیم. که کمی زمان بر است.

ct.fill_stocks_table()
ct.fill_stocks_prices_table()

اطلاعات هر اوراق اخزا را از دیتابیس گرفته و در متغیر ذخیره میکنیم.

df_info = ct.read_table_by_sql_query(
    """
    SELECT symbol, code, capital, name
    FROM stocks
    WHERE group_type IN ('I1', 'I2', 'I3', 'I4')
    """
)

df_info.head()
symbol code capital name
0 اخزا706 71772000724177515 27788137 اسنادخزانه-م6بودجه97-990423
1 اخزا4 71601274953984874 10000000 اسناد خزانه اسلامي950821
2 اخزا906 17166179154270422 15000000 اسنادخزانه-م6بودجه99-020321
3 اخزا8 20807724049262494 15000000 اسناد خزانه اسلامي960523
4 سخاب2 55500157187630757 16304000 اسناد خزانه اسلامي960822

قیمت هر اوراق را نیز از دیتابیس گرفته و در متغیر ذخیره میکنیم.

codes = ",".join(f"'{code}'" for code in df_info.code)

df_price = ct.read_table_by_sql_query(
    f"""
    SELECT code, symbol, date, volume, value, price
    FROM stocks_prices
    WHERE code IN ({codes})
    """
)

df_price.head()
code symbol date volume value price
0 10051281467803178 اخزا711 13970620000000 12520 10266400000 820000.0
1 10051281467803178 اخزا711 13970621000000 130020 105186250000 809001.0
2 10051281467803178 اخزا711 13970624000000 20 16188078 809404.0
3 10051281467803178 اخزا711 13970625000000 100000 81150000000 811500.0
4 10051281467803178 اخزا711 13970626000000 20000 16230000000 811500.0

دو داده را باهم ادغام میکنیم.

df = pd.merge(df_info, df_price, on=["symbol", "code"])
df.head()
symbol code capital name date volume value price
0 اخزا706 71772000724177515 27788137 اسنادخزانه-م6بودجه97-990423 13970710000000 92761 58629362000 632048.0
1 اخزا706 71772000724177515 27788137 اسنادخزانه-م6بودجه97-990423 13970711000000 9581 6083935000 635000.0
2 اخزا706 71772000724177515 27788137 اسنادخزانه-م6بودجه97-990423 13970714000000 11021 6987688093 634034.0
3 اخزا706 71772000724177515 27788137 اسنادخزانه-م6بودجه97-990423 13970715000000 13415 8534187500 636168.0
4 اخزا706 71772000724177515 27788137 اسنادخزانه-م6بودجه97-990423 13970716000000 150 97200000 648000.0

ستون‌های مورد نیاز برای محاسبه سود مرکب سالانه را می‌سازیم.

df["date"] = df["date"].str.removesuffix("000000")
df["due_date"] = [("14" if due_date < '900000' else "13")+due_date for due_date in df.name.str.extract(r'(\d{6})$')[0]]

df["jdate"] = df["date"].jalali.parse_jalali("%Y%m%d")
df["days"] = ((df["due_date"].jalali.parse_jalali("%Y%m%d") - df.jdate)/np.timedelta64(1, 'D')).astype(int)


df[["date", "jdate", "due_date", "days", "price"]].head()
date jdate due_date days price
0 13970710 1397-07-10 00:00:00 13990423 650 632048.0
1 13970711 1397-07-11 00:00:00 13990423 649 635000.0
2 13970714 1397-07-14 00:00:00 13990423 646 634034.0
3 13970715 1397-07-15 00:00:00 13990423 645 636168.0
4 13970716 1397-07-16 00:00:00 13990423 644 648000.0

فرمول محاسبه سود مرکب سالانه: \(YTM=(\cfrac{1,000,000}{Price})^{\cfrac{365}{days}}-1\)

df["ytm"] = ((1000000/df.price)**(365/df.days)-1)
df[["jdate", "days", "ytm"]].sort_values("ytm", ascending=False).head()
jdate days ytm
18792 1399-04-18 00:00:00 5 1.691812
4742 1400-05-16 00:00:00 3 1.344538
14838 1402-05-25 00:00:00 12 1.043377
11925 1397-06-31 00:00:00 10 0.920059
16271 1402-08-30 00:00:00 6 0.725785

برای اینکه سودهای کذایی که به دلیل نزدیکی اوراق‌ها به سررسید ایجاد می‌شود را حذف کنیم از کد زیر استفاده میکنیم.

df = df[df.days > 15]
df[["jdate", "days", "ytm"]].sort_values("ytm", ascending=False).head()
jdate days ytm
6946 1399-04-18 00:00:00 26 0.612420
10408 1399-04-01 00:00:00 51 0.558181
22723 1400-08-22 00:00:00 31 0.543943
969 1402-02-23 00:00:00 29 0.525546
31574 1402-09-19 00:00:00 36 0.452534

می‌توانیم برای درک بهتر داده‌ها از روش‌های آماری استفاده کنیم که ساده ترین‌ آنها محاسبه میانه و تعداد اوراق‌ها در هر روز است.

df_ytm = df[["jdate", "ytm"]].groupby("jdate").agg(["median", "count"])["ytm"]

df_ytm
median count
jdate
1394-07-08 00:00:00 0.260917 1
1394-07-11 00:00:00 0.267874 1
1394-07-12 00:00:00 0.258197 1
1394-07-13 00:00:00 0.256364 1
1394-07-14 00:00:00 0.257926 1
... ... ...
1403-03-08 00:00:00 0.340772 26
1403-03-09 00:00:00 0.339223 27
1403-03-12 00:00:00 0.347138 24
1403-03-13 00:00:00 0.350030 52
1403-03-16 00:00:00 0.344439 22

2083 rows × 2 columns

برای اینکه تخمینی از ارزش معاملات داشته باشیم از مجموع ارزش معاملات روزانه اوراق استفاده میکنیم. برای اینکه در نمودار بتوانیم نمایش دهیم بر حسب هزار میلیارد ریال بیان می‌کنیم..

df_value = df[["jdate", "value"]].groupby("jdate").agg(
    ["median", "sum", "mean"]
)["value"]

df_value["scale"] = df_value["sum"] / 1_000_000_000_000

df_value
median sum mean scale
jdate
1394-07-08 00:00:00 3.456456e+11 345645593500 3.456456e+11 0.345646
1394-07-11 00:00:00 5.292737e+10 52927371560 5.292737e+10 0.052927
1394-07-12 00:00:00 6.161099e+09 6161099076 6.161099e+09 0.006161
1394-07-13 00:00:00 8.790125e+09 8790125279 8.790125e+09 0.008790
1394-07-14 00:00:00 4.733399e+09 4733399398 4.733399e+09 0.004733
... ... ... ... ...
1403-03-08 00:00:00 4.359337e+10 3967297803570 1.525884e+11 3.967298
1403-03-09 00:00:00 6.005374e+09 1796449583630 6.653517e+10 1.796450
1403-03-12 00:00:00 2.700826e+10 2100827818530 8.753449e+10 2.100828
1403-03-13 00:00:00 3.646505e+11 646458345055983 1.243189e+13 646.458345
1403-03-16 00:00:00 1.245458e+10 1348158139350 6.127992e+10 1.348158

2083 rows × 4 columns

رسم نمودار

برای رسم نمودار می‌توان از کد زیر استفاده کرد که توضیح آن از بحث ما خارج است.

import plotly.express as px 
from plotly.subplots import make_subplots
import plotly.graph_objects as go

fig = make_subplots(specs=[[{"secondary_y": True}]])


fig.add_trace(
    go.Scatter(
        name="ارزش معاملات روزانه",
        x=df_value.index, y=df_value["scale"],
        stackgroup='one', 
        marker_size=1, 
        mode="lines",
        line_color='blue',
        hovertemplate='<b>ارزش معاملات (هزار میلیارد ریال)</b>: %{y:,.2f}<br><extra></extra>'
    ),
    secondary_y=True
)

fig.add_trace(
    go.Scatter(
        name="بازده سالانه (YTM)",
        x=df["jdate"], y=df["ytm"]*100,
        mode='markers',
        marker_color='orange', 
        marker_size=3, 
        customdata=np.stack((
                df["symbol"],
                df["date"], df["due_date"],
                df["days"], df["price"]
            ),
            axis=-1
        ),
        hovertemplate=("<br>".join(
            [
                s.replace(" ", " ")
                for s in [
                    '<b>نماد اوراق</b>: %{customdata[0]}',
                    '<b>تاریخ روز</b>: %{customdata[1]}',
                    '<b>سر رسید</b>: %{customdata[2]}',
                    '<b>روز باقیمانده</b>: %{customdata[3]}',
                    '<b>بازده (درصد)</b>: %{y:,.2f}',
                    '<extra></extra>'
                ]
            ]
        ))
    ),
    secondary_y=False
)

fig.add_trace(
    go.Scatter(
        name="میانه کل اوراق‌ها",
        x=df_ytm.index, y=df_ytm["median"]*100,
        line_color='black',
        mode='markers',
        marker_size=6, 
        hovertemplate='<b>میانه بازده (درصد)</b>: %{y:,.2f}<br><extra></extra>'
    ),
    secondary_y=False
)


fig.update_layout(
    font=dict(
        family="Vazirmatn",
        size=12,
        color="black"
    ),
    xaxis=dict(
        title="تاریخ",
        tickformat='%Y-%m-%d'
    ),
    yaxis=dict(
        title="""
        <br>
        بازده مرکب سالانه یا همان (درصد)
        <br>
        """,
        range=[0, 50]
    ),
    yaxis2=dict(
        title="""
        <br>
        مجموع ارزش معاملات (هزار میلیارد ریال)
        <br>
        نمایش لوگاریتمی
        """,
        range=[0, 10],
        type="log"
    ),
    hoverlabel=dict(align="right"),
    legend=dict(
        x=.1,
        y=.9,
        traceorder="normal",
        font=dict(
            family="Vazirmatn",
            size=12,
            color="black"
        ),
    )
)

fig.show()